﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;

namespace CashierSystem.CheckItemManager
{
    class CheckItemManager:DbConnection
    {
        public static void getDoctorInfo(out string[] doctorName, out string[] doctorId,string departId)
        {
            ArrayList aldoctorId = new ArrayList();
            ArrayList aldoctorName = new ArrayList();
            aldoctorId.Add("0");
            aldoctorName.Add(" ");

            string sql = "SELECT t_user.userId, t_user.name FROM t_user INNER JOIN t_userRole ON t_userRole.userId = t_user.userId INNER JOIN t_role ON t_role.roleId = t_userRole.roleId";
            sql +=  " WHERE (t_user.departId = '" + departId + "') AND (t_userRole.roleId = '4') OR (t_userRole.roleId = '2') OR (t_userRole.roleId = '5')";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader dbreader = cmd.ExecuteReader();
                while (dbreader.Read())
                {
                    aldoctorId.Add(dbreader["userId"].ToString());
                    aldoctorName.Add(dbreader["name"].ToString());
                }
                conn.Close();

            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            doctorId = new string[aldoctorId.Count];
            doctorName = new string[aldoctorName.Count];
            for (int i = 0; i < doctorId.Length; i++)
            {
                doctorId[i] = (string)aldoctorId[i];
                doctorName[i] = (string)aldoctorName[i];
            }
        }

        public static void saveItems(string itemName, string itemPrice)
        {
            string sql = "insert into t_items(itemName,price) values('" + itemName + "','" + itemPrice + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
        }

        public static string getMaxItemId()
        {
            string sql = "SELECT MAX(itemId) AS Expr1 FROM t_items";
            string maxItemId = "";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Close();
                conn.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    maxItemId = reader["Expr1"].ToString();
                }
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
            return maxItemId;

        }

        public static void saveDoctorItem(string doctorId,string itemId)
        {
            string sql = "insert into t_doctorItems(itemId,userId) values('" + itemId + "','" + doctorId + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }
 
        }

        public static void saveDepartItem(string departId, string itemId)
        {
            string sql = "insert into t_departItems(departId,itemId) values('" + departId + "','" + itemId + "')";
            try
            {
                conn.Close();
                conn.Open();  // 数据库连接，父类里的静态变量，已经被初始化
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();//插入数据库
                conn.Close();
            }
            catch (OleDbException ex)
            {
                string s = ex.ToString();
            }

        }

        public static void getItemList(ListView lv,string departId,string itemName)
        {
            string sql = "SELECT t_department.departName, t_items.itemName, t_items.price, t_user.name FROM t_items INNER JOIN t_departItems ON t_departItems.itemId = t_items.itemId";
            sql += " INNER JOIN t_department ON t_departItems.departId = t_department.departId LEFT OUTER JOIN t_doctorItems ON t_doctorItems.itemId = t_items.itemId";
            sql += " LEFT OUTER JOIN t_user ON t_doctorItems.userId = t_user.userId ";
            if (departId != "")
            {
                sql += " and departId = '" + departId + "'";

            }
            if (itemName != "")
            {
                sql += " and itemName like '%" + itemName + "%'";

            }
            ColumnHeader[] ch = new ColumnHeader[4];
            ch[0] = new ColumnHeader("所属科室", 120, HorizontalAlignment.Left);
            ch[1] = new ColumnHeader("项目名称", 120, HorizontalAlignment.Left);
            ch[2] = new ColumnHeader("价格", 120, HorizontalAlignment.Left);
            ch[3] = new ColumnHeader("医师", 120, HorizontalAlignment.Left);
            Utils.dbReaderFillListView(lv, ch, sql, conn);
        }



    }
}
